/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package primephysique;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author AP
 */
public class Revenue extends javax.swing.JPanel {

    /**
     * Creates new form Revenue
     */
    public Revenue() {
        initComponents();
        showResult();
    }

    private String getDayOfMonth(int month, int year) {
        String day = "";
        switch (month) {
            case 1:
            case 3:
            case 5:
            case 7:
            case 8:
            case 10:
            case 12: {
                day = "31";
                break;
            }
            case 4:
            case 6:
            case 9:
            case 11: {
                day = "30";
                break;
            }
            case 2:
                if ((year % 4) == 0) {
                    day = "29";
                } else {
                    day = "28";
                }
        }
        return day;
    }

    private void showResult() {
        try {
            cdb = new ConnectDB();
            con = cdb.getConnection();
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            int m = cbbMonth.getSelectedIndex();
            int y = cbbYear.getSelectedIndex();
            String year = cbbYear.getSelectedItem().toString().trim();
            //         
            java.util.Date cdate = new Date();
            Timestamp date = new java.sql.Timestamp(cdate.getTime());
            //   System.out.println(date);
            // get range of infomation
            String query = "";
            DateFormat df = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
            String select = "";
            Date selectedDate = null;
            if (m == 0) {
                if (y == 0) {
                    query = "SELECT BranchID,sum(Fee)as revenue FROM Class WHERE"
                            + " StartDate< '" + date + "'" + "GROUP BY BranchID";
                    query="SELECT BranchID,sum(Fee)as revenue FROM Class,Trainee WHERE Class.ClassID=Trainee.ClassID GROUP BY BranchID";
                } else {
                    select = "01/01/" + year + " 00:00:00.0";
                    //   System.out.println(select);
                    selectedDate = df.parse(select);
                    date = new java.sql.Timestamp(selectedDate.getTime());
                    //   System.out.println(da);
                    query = "SELECT BranchID,sum(Fee)as revenue FROM Class WHERE "
                            + "StartDate> '" + date + "' AND StartDate<'" + year + "-12-31 23:59:59.999'" + "GROUP BY BranchID";
                    //       System.out.println(query);
                   query="SELECT BranchID,sum(Fee)as revenue FROM Class,Trainee WHERE Class.ClassID=Trainee.ClassID AND "
                         + "StartDate> '" + date + "' AND StartDate<'" + year + "-12-31 23:59:59.999'" + "GROUP BY BranchID";
                }
            } else {
                if (y == 0) {

                    //if get all year
                    // if(tes%2==0){
                    JOptionPane.showMessageDialog(null, "Please choose year !");
                    cbbMonth.setSelectedIndex(0);
                    //   tes++;
                    //  }
                    return;
                } else {
                    select = m + "/01/" + year + " 00:00:00";
                    selectedDate = df.parse(select);
                    date = new java.sql.Timestamp(selectedDate.getTime());
                    query = "SELECT BranchID,sum(Fee)as revenue FROM Class WHERE "
                            + "StartDate> '" + year + "-" + m + "-01 00:00:00.0'"
                            + "AND StartDate<'" + year + "-" + m + "-" + getDayOfMonth(m, Integer.parseInt(year)) + " 23:59:59.999'" + "GROUP BY BranchID";
                }
            }
            //add to table
            rs = st.executeQuery(query);
            rsmd = rs.getMetaData();
            Vector<String> columnName = new Vector<String>();
            Vector<String> columnData;
            //get column name
            columnName.add("");
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                columnName.add(rsmd.getColumnName(i + 1));
            }
            // set table column's Name
            model = new DefaultTableModel();
            int cc = rsmd.getColumnCount() + 1;
            for (int i = 0; i < cc; i++) {
                model.addColumn(columnName.get(i));
            }
            int index = 1;
            while (rs.next()) {
                columnData = new Vector<String>();
                columnData.add("" + index);
                //add branch name
                query = "SELECT BranchName FROM Branch WHERE BranchID ='" + rs.getString("BranchID") + "'";
                ResultSet brName = con.createStatement().executeQuery(query);

                brName.next();
                columnData.add(brName.getString("BranchName"));
                //branch revenue
                columnData.add(rs.getString("revenue") + " $");
                model.addRow(columnData);
                //         columnData.clear();
                index++;
            }
            //sum total
            model.addRow(new Vector());
            Vector total = new Vector();
            total.add("");
            total.add("Total");
            //get data from db
            if (m == 0) {
                if (y == 0) {
                    query = "SELECT sum(Fee)as revenue FROM Class ";
                } else {
                    query = "SELECT sum(Fee)as revenue FROM Class WHERE"
                            + " StartDate> '" + year + "-01-01 00:00:00.0'"
                            + "AND StartDate< '" + year + "-12-31 23:59:59.999'";
                }
            } else {
                query = "SELECT sum(Fee)as revenue FROM Class WHERE "
                        + "StartDate> '" + year + "-" + m + "-01 00:00:00.0'"
                        + " AND StartDate<'" + year + "-" + m + "-" + getDayOfMonth(m, Integer.parseInt(year)) + " 23:59:59.999'";
            }

            rs = st.executeQuery(query);
            // set text to label
            rs.next();
            total.add(rs.getString(1) + " $");
            model.addRow(total);
            tbRevenue.setModel(model);
            scpRevenue.setViewportView(tbRevenue);

            /*
             * SimpleDateFormat df=new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
             * java.util.Date cdate=df.parse(res.getParameter(values));
             * java.sql.Timestamp da=new java.sql.Timestamp(cdate.getTime());
             *
             * DateFormat df=new SimpleDateFormat("dd/MM/yyyy hh:mm:ss");
             * java.util.Date cdate=df.parse("20/12/1984 12:23:23"); 
             * Timestamp da=new java.sql.Timestamp(cdate.getTime());
             * System.out.println(da);
             *
             */


            con.close();


        } catch (Exception ex) {

            ex.printStackTrace();
        }
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        lbMonth = new javax.swing.JLabel();
        cbbMonth = new javax.swing.JComboBox();
        scpRevenue = new javax.swing.JScrollPane();
        tbRevenue = new javax.swing.JTable();
        lbYear = new javax.swing.JLabel();
        cbbYear = new javax.swing.JComboBox();

        lbMonth.setText("Month :");

        cbbMonth.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }));
        cbbMonth.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbbMonthItemStateChanged(evt);
            }
        });

        tbRevenue.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        scpRevenue.setViewportView(tbRevenue);

        lbYear.setText("Year :");

        cbbYear.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "All", "2012", "2011", "2010" }));
        cbbYear.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                cbbYearItemStateChanged(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(37, 37, 37)
                        .addComponent(lbMonth)
                        .addGap(18, 18, 18)
                        .addComponent(cbbMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(35, 35, 35)
                        .addComponent(lbYear)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(cbbYear, javax.swing.GroupLayout.PREFERRED_SIZE, 74, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(scpRevenue, javax.swing.GroupLayout.PREFERRED_SIZE, 428, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(0, 20, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(14, 14, 14)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lbMonth)
                    .addComponent(cbbMonth, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(lbYear)
                    .addComponent(cbbYear, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 79, Short.MAX_VALUE)
                .addComponent(scpRevenue, javax.swing.GroupLayout.PREFERRED_SIZE, 188, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(25, 25, 25))
        );
    }// </editor-fold>//GEN-END:initComponents

    private void cbbMonthItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbbMonthItemStateChanged
        //    System.out.println("m=" + cbbMonth.getSelectedIndex());
        if (evt.getStateChange() == 1) {
            showResult();
        }
    }//GEN-LAST:event_cbbMonthItemStateChanged

    private void cbbYearItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_cbbYearItemStateChanged
        //       System.out.println("y=" + cbbYear.getSelectedItem().toString().trim());
        if (evt.getStateChange() == 1) {
            showResult();
        }
    }//GEN-LAST:event_cbbYearItemStateChanged
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JComboBox cbbMonth;
    private javax.swing.JComboBox cbbYear;
    private javax.swing.JLabel lbMonth;
    private javax.swing.JLabel lbYear;
    private javax.swing.JScrollPane scpRevenue;
    private javax.swing.JTable tbRevenue;
    // End of variables declaration//GEN-END:variables
    private ConnectDB cdb = null;
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private ResultSetMetaData rsmd;
    private DefaultTableModel model = new DefaultTableModel();
    int tes = 0;
}
